[TOC]

SQL의 분류

SQL 분류는 크게 DML, DDL, DCL로 분류된다.

DML(Data Manipulation Language)

데이터를 조작(선택, 삽입, 수정, 삭제) 하는데 사용되는 언어.

DML 구문이 사용되는 대상은 테이블의 행이다. DML을 사용하기 전에 테이블이 정의되어 있어야 된다.

Select, Insert, Update, Delete, 트랜젝션이 이에 해당한다.

트랜젝션은 테이블의 데이터를 변경할때 실제 테이블에 완전히 적용하지 않고, 임시로 적용하고, 실수가 있을때에는 롤백시키고 성공하면 그제서야 데이터에 반영이 된다.

DDL(Data Definition Language)

DB, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성, 삭제, 변경하는 역할을 한다.

CREATE, DROP, ALTER 등이 있다. DDL은 트랜젝션을 발생시키지 않는다. 따라서 롤백이나 커밋을 시킬수 없다. DDL문은 즉시 MySQL에 적용된다.

DCL(Data Control Language)

사용자에게 어떤 권한을 부여하거나 빼앗을때 주로 사용되는 구문이다.

GRANT / REVOKE / DENY 등이 있다.

select 문

원하는 데이터를 가져와 주는 SQL문

1
2
3
4
5
6
select select_expr 
	[from table_reference]
	[where where_condition]
	[group by {col_name | expr | position}]
	[having where_condition]
	[order by {col_name | expr | position}]

use 문

사용할 데이터베이스 구문

use 데이터베이스_이름;

1
SQL은 대소문자를 구분하지 않는다. 하지만 여러가지 측면에서 전체 대문자 또는 소문자로 통일하여 구문을 읽기 쉽게하고, MySQL 성능에도 약간의 도움이 된다.

Select & from

1
2
// * wildcard 모든것
select * from titles;

DB 이름, 테이블 이름, 필드 이름이 생각나지 않을때, 조회하는 방법.

1
2
3
4
5
6
7
-- 현재 서버에 어떤 데이터베이스가 있는지 조회
show databases; 
-- 현재 데이터베이스에 있는 테이블 조회
use DB이름;
show table status;
-- 해당 테이블의 열에 무엇이 있는지 조회
DESCRIBE 테이블  | desc 테이블

예제 테이블 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create database sqlDB;

use sqlDB;
create table userTbl
(
	userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null,
    addr char(2) not null,
    mobile1 char(3),
    mobile2 char(8),
    height smallint,
    mData DATE
);

create table buyTbl
(
	num int AUTO_INCREMENT not null PRIMARY KEY,
    userID CHAR(8) not null,
    prodName char(6) not null,
    groupName char(4),
    price int not null,
    amount SMALLINT not null,
    FOREIGN KEY (userID) REFERENCES userTbl(userID)
);

DB 개체의 이름을 식별자라고 한다. DB 개체란 데이터베이스, 테이블, 인덱스, 열, 뷰, 트리거, 스토어드 프로시저 등과 같은 개체들을 의미한다.
알파벳 a~z, A~Z, 0~9 $,_을 사용할수 있다.
개체 이름은 최대 64자로 제한된다.
예악어를 사용하면 안된다.
원칙적으로 중간에 공백이 있으면 안되지만 꼭 사용하려면 으로 묶어야 된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
insert into userTbl values ('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
insert into userTbl values ('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
insert into userTbl values ('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2012-4-4');
insert into userTbl values ('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2012-4-4');
insert into userTbl values ('SSK', '성시경', 1979, '서울', NULL, NULL, 186, '2012-4-4');
insert into userTbl values ('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2012-4-4');
insert into userTbl values ('YJS', '윤종신', 1969, '경남', NULL, NULL, 170, '2012-4-4');
insert into userTbl values ('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2012-4-4');
insert into userTbl values ('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2012-4-4');
insert into userTbl values ('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2012-4-4');


insert into buytbl values (NULL, 'KBS', '운동화', NULL, 30, 2);
insert into buytbl values (NULL, 'KBS', '노트북', '전자', 1000, 1);
insert into buytbl values (NULL, 'JYP', '모니터', '전자', 200, 1);
insert into buytbl values (NULL, 'BBK', '모니터', '전자', 200, 5);
insert into buytbl values (NULL, 'KBS', '청바지', '의류', 50, 3);
insert into buytbl values (NULL, 'BBK', '메모리', '전자', 80, 10);
insert into buytbl values (NULL, 'SSK', '책', '서적', 15, 5);
insert into buytbl values (NULL, 'EJW', '책', '서적', 15, 2);
insert into buytbl values (NULL, 'EJW', '청바지', '의류', 50, 1);
insert into buytbl values (NULL, 'BBK', '운동화', NULL, 30, 2);
insert into buytbl values (NULL, 'EJW', '책', '서적', 15, 1);
insert into buytbl values (NULL, 'BBK', '운동화', NULL, 30, 2);

회원 테이블의 결과 순서가 입력한 순서와 다른 이유는 userID를 PRIMARY key 로 지정됬기 때문에, 자동으로 클러스터형 인덱스가 생성되어 입력 시에 userID열로 정렬되기 때문이다.

특정한 조건의 데이터만 조회 ( select … from … where)

기본적 where 절

1
2
3
select 필드이름 from 테이블이름 where 조건식

select * from userTbl where name = '김경호

관계 연산자의 사용

  1. 1970년 이후 출생하고 신장이 182 이상인 사람의 아이디와 이름 조회

    1
    select userID, name from userTbl where birthYear >= 1970 AND height >= 182;

  2. 1970년 이후 출생했거나 신장이 182 이상인 사람의 아이디와 이름 조회

    1
    select userID, name from userTbl where birthYear >= 1970 OR height >= 182;

조건연산자 =, <, >, <=, >=, <>, != 등과 관계 연산자 NOT, AND, OR 등을 잘 조합해서 쿼리를 만든다.

Between .. And와 In() 그리고 LIKE

  1. 키가 180 ~ 183인 사람 조회

    1
    2
    select Name, height from userTbl where height >= 180 AND height <= 183;
    select Name, height from userTbl where height BETWEEN 180 AND 183;

위 아래 같은 쿼리문이다. 키의 경우 숫자로 구성되어 있어 연속적인 값을 가지고 있으므로 Between ~ And 표현이 가능하지만. 지역의 같은 칼럼의 경우에는 연속된 값이 아니기 때문에 Between ~ And 표현이 불가능연속적이지 않은 이산적인 값을 위한 연산자가 IN()이다.

  1. 지역이 경남, 전남, 경북인 사람의 정보를 확인

    1
    2
    select Name, addr from userTbl where addr = '경남' OR addr = '전남' OR addr = '경북'
    select Name, addr from userTbl where addr IN('경북', '경남', '전남')

문자열의 내용을 검색하기 위해서는 LIKE 연산자를 사용할수 있다. %는 무엇이든 허용한다는 뜻이다. 한글자와 매치하기 위해서 _을 사용한다.

  1. 성이 김씨인 사람을 검색

    1
    select Name, height from userTbl where name LIKE '김%';

    ​

  2. 이름이 종신으로 끝나는 사람을 검색

    1
    select Name, height from userTbl where name LIKE '_종신';

%, _가 문자열에 제일 앞에 있다면 MySQL 성능에 나쁜 영향을 끼칠수 있다. 이 경우
인덱스를 사용하지 않고 전체 데이터를 검색한다.

ANY/ALL/SOME, 서브쿼리

  1. 김경호보다 키가 크거나 같은 사람의 이름과 키를 출력

    1
    2
    3
    select Name, height from userTbl where height > 177; -- 김경호의 키를 하드코딩
    select Name, height from userTb1 where height > 
    (select height from userTb1 where Name = '김경호'); -- 이 부분이 서브쿼리

  2. 지역이 ‘경남’인 사람의 키보다 크거나 같은 사람을 추출.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select Name, height from userTbl where height >= 
    (select height from userTbl where addr = '경남') -- 이 경우, 두개의 값 이상이 리턴되므로 에러가 난다.

    select Name, height from userTbl where height >= 
    ANY (select height from userTb1 where addr = '경남')  -- ANY, SOME은 서브쿼리의 여러개중 한가지만 만족해도 된다.

    -- IN과 =ANY는 같다.
    select Name, height from userTbl where height >= ALL 
    (select height from userTbl where addr = '경남')  -- ALL은 전부 만족해야 출력된다.

원하는 순서대로 정렬하여 출력 ORDER BY

결과가 출력되는 순서를 조절한다.

1
2
3
select Name, height from userTbl order by mData; -- 기본은 오름차순이다.
select Name, height from userTbl order by height DESC, name ASC;
-- 꼭 ORDER BY 열이 select 다음에 꼭 있을 필요가 없음.

중복된 것은 하나만 남기는 DISTINCT

  1. 회원 테이블에서 회원들의 거주지역이 몇군데인지 확인

    1
    2
    3
    4
    select distinct addr from userTbl;
    /* 테이블은 employees 사용 */
    select emp_no, hire_date from employees order by hire_date asc limit 5  -- 5개 제한
    select emp_no, hire_date from employees order by hire_date asc limit 0, 5  -- LIMIT 개수 OFFSET 시작

악성 쿼리문이란, 서버의 처리량을 많이 사용해서 서버의 전반적인 성능을 나쁘게한다. 잘못된 악성쿼리를 자꾸 만들지 않도록 노력해야된다.

테이블을 복사하는 CREATE TABLE ~ SELECT

1
CREATE TABLE 새로운 테이블 (SELECT 복사할열 FROM 기존테이블)

  1. buyTbl을 buyTbl2로 복사하는 구문

    1
    Create Table buyTbl2 (select * from buyTbl);

  2. 일부열만 복사할수도 있다.

    1
    Create Table buyTbl3 (select userID, prodName from buyTbl);

하지만 복사하게 될때, 기본키와 외래키의 제약조건들은 복사되지 않는다.

GROUP BY, Having, 집계 함수

GROUP BY

그룹으로 묶어주는 역할을 한다.

1
2
3
4
5
6
7
8
9
Select userID, amount From buyTbl ORDER BY userID;
-- 여러 번의 물건 구매가 각각의 행으로 출력된다. 
select userID, SUM(amount) from buyTbl GROUP BY userId;
-- 보기쉽게 나왓다.
select userID as '사용자 아이디', SUM(amount) as '총 구매 갯수' from buyTbl GROUP BY userId;
-- 별칭을 통해 결과를 보기 편하게 만듬.

-- 구매액의 총합
select userID as '사용자 아이디', SUM(amount * price) as '총 구매액'  from buyTbl GROUP BY userId;

집계 함수

함수명 설명
AVG() 평균
MIN() 최소값
MAX() 최대값
COUNT() 행의 갯수
COUNT(DISTINCT()) 행의 갯수(중복은 1개만 인정한다.)
STDEV() 표준편차
VAR_SAMP() 분산을 구한다.

  1. 전체 구매자가 구매한 물품의 갯수의 평균

    1
    select avg(amount) from buytbl;

  2. 각 사용자별로 구매한 물품의 갯수의 평균

    1
    select userID, avg(amount) from buytbl group by userId;

  3. 가장 큰 키와 가장 작은 키의 회원 이름과 키를 출력

    1
    2
    3
    4
    select Name, MAX(height), MIN(height) from usertbl; -- 이름이 하나만 나온다.
    select Name, MAX(height), MIN(height) from usertbl group by userID; -- 모든 유저에 대해서 나오게 되버린다.
    select Name, height from usertbl 
    	where height = (select max(height) from usertbl) OR height = (select min(height) from usertbl);

  4. 휴대폰이 있는 사용자 수를 카운트

    1
    select count(mobile1) from usertbl;

Having 절

WHERE 구문에서 집계함수를 쓸수 없는데, Having 절로 안에 쓰면 된다.

1
2
3
4
5
6
7
8
9
10
select userID as '사용자', SUM(price * amount) as '총 구매액' from buytbl 
group by userId
having SUM(price * amount) > 1000;

-- 추가로 적은 사용자부터 나타내려면 다음과 같이하면된다.

select userID as '사용자', SUM(price * amount) as '총 구매액' from buytbl 
group by userId
having SUM(price * amount) > 1000
order by SUM(price * amount);

INSERT 문

테이블에 데이터를 삽입한다.

1
INSERT [INTO] 테이블[(열1 .... 열n)] VALUES (값1 ... 값N);  -- []은 생략가능하다.

AUTO_INCREMENT

테이블 속성이 AUTO_INCREMENT로 지정되어 있다면 INSERT에서는 해당 열이 없다고생각하고 입력하면 된다. AUTO_INCREMENT는 자동으로 1부터 증가하는 값을 입력해준다.

AUTO_INCREMENT로 지정할때에는 PRIMARY KEY 또는 UNIQUE로 지정해줘야 한다.

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE testTbl2
(
    id int AUTO_INCREMENT PRIMARY KEY,
    userName char(3),
    age int
);


insert into testTbl2 values (NULL, '지민', 25);
insert into testTbl2 values (NULL, '유나', 22);
insert into testTbl2 values (NULL, '유경', 21);

계속입력을 하다보면 어느 숫자까지 확인할 필요가 있는데 SELECT LAST_INSERT_ID() 쿼리를 사용하면 마지막에 입력된 값을 보여준다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ALTER TABLE testTbl2 AUTO_INCREMENT = 100; -- 이후 AUTO_INCREMENT는 100부터 입력된다.

/*=====================================================*/
CREATE TABLE testTbl2
(
    id int AUTO_INCREMENT PRIMARY KEY,
    userName char(3),
    age int
);
ALTER TABLE testTbl2 AUTO_INCREMENT = 100;
SET @@auto_increment_increment = 3; -- 3씩 증가하게 된다.


-- 3건의 데이터를 한 문장으로 입력가능하다.
insert into testTbl2 values (NULL, '지민', 25), (NULL, '유나', 22), (NULL, '유경', 21);

대량의 샘플 데이터 생성

1
2
INSERT INTO 테이블이름 (열이름1 ... 열이름N)
	SELECT문

  1. SELECT 문의 결과 열의 갯수는 INSERT 할 테이블의 열 갯수와 일치해야됨.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    CREATE TABLE testTBl4 
    (
    	id int,
        Fname varchar(50),
        Lname varchar(50)
    );

    INSERT INTO testTBl4
    	select emp_no, first_name, last_name from employees.employees;
    	
    CREATE TABLE testTBl5
    	(select emp_no, first_name, last_name from employees.employees);

조건부 데이터 입력

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
create table memberTBL 
	(select userID, name, addr from userTbl limit 3);  -- 3건만 가져온다.

alter table memberTBL
	add CONSTRAINT pk_memberTBL PRIMARY KEY (userID);  -- PK를 지정한다.
    
select * from memberTBL;

-- 1. 데이터를 추가로 3건 입력
insert into memberTBL values ('BBK', '비비코', '미국'); -- PK가 중복되었다. -> 조회하면 나머지 2개도 적용이 안됨.
insert into memberTBL values ('SJH', '서장훈', '서울');
insert into memberTBL values ('HJY', '한주엽', '경기');

-- 2. INSERT IGNORE 문으로 대체
insert IGNORE into memberTBL values ('BBK', '비비코', '미국'); -- 이 항목만 에러가 뜨고 나머지 정상 입력됨.
insert IGNORE into memberTBL values ('SJH', '서장훈', '서울');
insert IGNORE into memberTBL values ('HJY', '한주엽', '경기');

-- 3. 기본키가 중복이라면 데이터를 수정하게 만들기
insert into memberTBL values('BBK', '비비코', '미국')
	ON DUPLICATE KEY UPDATE name = '비비코', addr = '미국'; -- 이 항은 중복이므로 update문이 실행됨. 
-- ON DUPLICATE KEY UPDATE은 PK가 중복되지 않으면 INSERT가되고 
-- 아니라면 update문이 실행된다.

insert into memberTBL values ('DJM', '동짜몽', '일본')
    ON DUPLICATE KEY UPDATE name = '동짜몽', addr = '일본';

UPDATE 문

기존의 입력값을 변경한다.

1
2
3
UPDATE 테이블이름
	SET 열1 = 값1, 열2 = 값2 ...
	WHERE 조건 ;
1
2
3
4
5
update testtbl4
	set Lname = '없음'
	where Fname = 'kyichi';
	
update buyTbl set price = price * 1.5; -- 테이블 전체를 변경할때에는 where문이 없어도 된다.

DELETE FROM

DELETE는 행 단위로 삭제한다.

1
DELETE FROM 테이블이름 WHERE 조건;
1
2
3
4
5
6
7
8
9
delete from testTbl4 where fname = 'Aamer'; -- 여기에 LIMIT 옵션을 넣는다면  그만큼만 삭제한다.
delete from testTbl4 where fname = 'Aamer' LIMIT 5;

-------------------------------------------------------------------------
delete from bigtbl1; -- DML문은 delete는 트랜젝션 로그를 기록하는 작업때문에 느리다.
Drop table bigTbl2; -- DDL은 트랜젝션을 일으키지 않는다 그로므로 빠르다.
Truncate table bigTbl3; -- TURNCATE 문 효과는 delete를 하지만 트랜젝션 로그를 남기지 않는다. 테이블의 구조를 남겨놓고 삭제하고 싶다면 이거로 삭제하자.

TRUNCATE > DROP > DELETE